package com.xayy.ha.gen.lite.mapper;


import com.xayy.ha.gen.lite.entity.GenTabField;
import com.xayy.ha.gen.lite.entity.GenTabInfo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Component;

import java.util.List;


/**
 * @ClasssName CodeGenerateMapper
 * @Description 代码生成mapper
 * @Author zyw
 * @Date 2021/5/12
 * @Version V0.0.1
 */
@Mapper
@Component
public interface DmDbTableMapper {


    @Select("<script>" +
            "SELECT \n" +
            "    cols.COLUMN_NAME AS columnName, \n" +
            "    cols.DATA_TYPE AS dataType, \n" +
            "    com.COMMENTS AS fieldComment, \n" +
            "    (CASE WHEN cons.CONSTRAINT_TYPE = 'P' THEN 'PRI' ELSE '' END) AS columnKey, \n" +
            "    (CASE WHEN cols.NULLABLE = 'Y' THEN 'YES' ELSE 'NO' END) AS isNullable, \n" +
            "    cols.DATA_TYPE || \n" +
            "    CASE \n" +
            "        WHEN cols.DATA_TYPE IN ('NUMBER','DECIMAL') AND cols.DATA_SCALE > 0 \n" +
            "            THEN '(' || cols.DATA_PRECISION || ',' || cols.DATA_SCALE || ')' \n" +
            "        WHEN cols.DATA_TYPE IN ('VARCHAR2','CHAR') \n" +
            "            THEN '(' || cols.DATA_LENGTH || ')' \n" +
            "        WHEN cols.DATA_PRECISION IS NOT NULL \n" +
            "            THEN '(' || cols.DATA_PRECISION || ')' \n" +
            "        ELSE '' \n" +
            "    END AS columnType \n" +
            "FROM \n" +
            "    USER_TAB_COLUMNS cols \n" +
            "LEFT JOIN USER_COL_COMMENTS com \n" +
            "    ON cols.TABLE_NAME = com.TABLE_NAME \n" +
            "    AND cols.COLUMN_NAME = com.COLUMN_NAME \n" +
            "LEFT JOIN ( \n" +
            "    SELECT \n" +
            "        cc.COLUMN_NAME, \n" +
            "        c.CONSTRAINT_TYPE \n" +
            "    FROM \n" +
            "        USER_CONS_COLUMNS cc \n" +
            "    JOIN USER_CONSTRAINTS c \n" +
            "        ON cc.CONSTRAINT_NAME = c.CONSTRAINT_NAME \n" +
            "        AND cc.TABLE_NAME = c.TABLE_NAME \n" +
            "    WHERE \n" +
            "        c.TABLE_NAME = #{tableName} \n" +
            "        AND c.CONSTRAINT_TYPE = 'P' \n" +
            ") cons \n" +
            "    ON cols.COLUMN_NAME = cons.COLUMN_NAME \n" +
            "WHERE \n" +
            "    cols.TABLE_NAME = #{tableName} \n" +
            "ORDER BY \n" +
            "    cols.COLUMN_ID" +
            "</script>")
    List<GenTabField> selectTableColumn(@Param("tableName") String tableName);


    /**
     * 查询表信息
     *
     * @param tableName 表名称
     * @return
     */
    @Select("" +
            " SELECT " +
            "   TABLE_NAME AS tableName, " +
//            "   'N/A' AS engine, " +  // 达梦无存储引擎概念，固定返回'N/A'
            "   COMMENTS AS tableComment" +
//            "   CREATE_TIME AS createTime " +  // 直接使用CREATE_TIME字段
            " FROM ALL_TAB_COMMENTS " +
            " WHERE  TABLE_NAME = #{tableName} " +
            "")
    GenTabInfo selectTableInfo(@Param("tableName") String tableName);
}